﻿CREATE PROCEDURE [dbo].[proc_OrderId_Getlist_In]
(
	@InCompanyId varchar(500),
	@TypeId int,
	@WayId int,
	@ProcessId int,
	@Stext nvarchar(50),
	@StartDate nvarchar(10),
	@EndDate nvarchar(10),
	@StartIndex int,
	@EndIndex int
)
AS
Begin
Declare @cSql varchar(2000)

Set @cSql = 'StateId>1'
if @TypeId<>0
	Set @cSql = @cSql + ' And TypeId='+Convert(varchar(10),@TypeId)

if @WayId<>0
	Set @cSql = @cSql + ' And WayId='+Convert(varchar(10),@WayId)

if @ProcessId<>0
	Set @cSql = @cSql + ' And ProcessId='+Convert(varchar(10),@ProcessId)

if @Stext<>''
	Set @cSql = @cSql + ' And (CusName like ''%'+@Stext+'%'' Or Salesman like ''%'+@Stext+'%'')'

Set @cSql=';WITH list As(Select ROW_NUMBER() OVER (Order By OrderId Desc,id Desc) AS Row
		,Id
		,OId
		,OrderId
		,CusId
		,CusName
		,CusPerson
		,CusPersonTel
		,CusDescription
		,CusEstimate
		,TypeId
		,TypeName
		,WayId
		,WayName
		,ProcessId
		,ProcessName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,CreateDate
		,YingShouPrice
		,IsNull(ShiShouPrice,0) As ShiShouPrice
		,Discount
		,PaymentTypeId
		,PaymentType
		,BankId
		,Bank
		,PaymentDescription
		,OtherDescription
		,DepId
		,CompanyId
		,StateId
,IsGreen
		,IsNull((Select Title From Order_State Where Id=[Order].StateId),''---'') As StateName
		From [Order]
		Where ' + @cSql + ' And CompanyId In(' + @InCompanyId + ')
		And Left(OrderId,8)>=Replace(''' + @StartDate + ''',''-'','''') And Left(OrderId,8)<=Replace(''' + @EndDate + ''',''-'','''')
--		and Convert(nchar(10),FinanceRenlingDate,120) between ''' + @StartDate + ''' and ''' + @EndDate + '''
--And Left(OrderId,8)=Convert(varchar(10),FinanceRenlingDate,112)
	)
	
	Select *,(Select Count(0) From list) As RecordCount,(select sum(YingShouPrice) from list) as DataSum
	 From list Where Row Between ' + Convert(varchar(10),@StartIndex) + ' And ' + Convert(varchar(10),@EndIndex) + ' Order By Row'

	Exec(@cSql)
End
